﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Text;

namespace spk_eAuction.Classes
{
    public class DataAccess
    {
        public static string __DefaultConnectionString = WebConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();

        public static SqlConnection GetConnection()
        {
            return GetConnection(__DefaultConnectionString);
        }

        public static SqlConnection GetConnection(string strConn)
        {
            SqlConnection conn = new SqlConnection(strConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            return conn;
        }

        public static string GetEncryptedPassword(string password)
        {
            MD5CryptoServiceProvider md5Hasher = new MD5CryptoServiceProvider();
            byte[] hashedBytes;
            UTF8Encoding encoder = new System.Text.UTF8Encoding();
            hashedBytes = md5Hasher.ComputeHash(encoder.GetBytes(password));
            return BitConverter.ToString(hashedBytes);
        }

        public static DataRow GetDataRow(DataTable dt, string colName, object value)
        {
            foreach (DataRow row in dt.Rows)
            {
                if (row[colName].Equals(value))
                    return row;
            }
            return null;
        }

        #region Execute query without parameters
        public static DataTable Execute(string name, CommandType type)
        {
            // data table
            DataTable dt = new DataTable();
            // connection
            SqlConnection conn = GetConnection();
            // command
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = type;
            cmd.CommandText = name;
            cmd.Connection = conn;
            // adapter
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            try
            {
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                // MessageBox.Show(ex.ToString());
            }
            return dt;
        }
        // Select data from a table using stored procedure
        public static DataTable ExecuteProcedure(string storedProcedureName)
        {
            return Execute(storedProcedureName, CommandType.StoredProcedure);
        }
        // Select data from a table using query string
        public static DataTable ExecuteQuery(string queryName)
        {
            return Execute(queryName, CommandType.Text);
        }
        #endregion

        #region Execute query with parameters
        public static DataTable Execute(string name, SqlParameter[] _params, CommandType type)
        {
            // data table
            DataTable dt = new DataTable();
            // connection
            SqlConnection conn = GetConnection();
            // command
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = type;
            cmd.CommandText = name;
            cmd.Connection = conn;
            cmd.Parameters.AddRange(_params);
            // adapter
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            try
            {
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                // MessageBox.Show(ex.ToString());
            }
            return dt;
        }
        // Execute query with parameters using query string
        public static DataTable ExecuteQuery(string queryName, params SqlParameter[] _params)
        {
            return Execute(queryName, _params, CommandType.Text);
        }
        // Execute query with parameters using stored procedure
        public static DataTable ExecuteProcedure(string storedProcedureName, params SqlParameter[] _params)
        {
            return Execute(storedProcedureName, _params, CommandType.StoredProcedure);
        }

        #endregion

        #region Execute non-query without parameters
        public static int ExecuteNonQuery(string name, CommandType type)
        {
            SqlConnection conn = GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = type;
            cmd.CommandText = name;
            cmd.Connection = conn;
            int count = 0;
            try
            {
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                // MessageBox.Show(ex.ToString());
            }

            return count;
        }
        // Execute non-query without parameters using query string
        public static int ExecuteNonQuery(string queryName)
        {
            return ExecuteNonQuery(queryName, CommandType.Text);
        }
        // Execute non-query without parameters using stored procedure
        public static int ExecuteProcNonQuery(string storedProcedureName)
        {
            return ExecuteNonQuery(storedProcedureName, CommandType.StoredProcedure);
        }
        #endregion

        #region Execute non-query with parameters
        public static int ExecuteNonQuery(string name, SqlParameter[] _params, CommandType type)
        {
            SqlConnection conn = GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = type;
            cmd.CommandText = name;
            cmd.Connection = conn;
            cmd.Parameters.AddRange(_params);
            int count = 0;
            try
            {
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                // MessageBox.Show(ex.ToString());
            }

            return count;
        }
        // Execute non-query with parameters using query string
        public static int ExecuteNonQuery(string queryName, params SqlParameter[] _params)
        {
            return ExecuteNonQuery(queryName, _params, CommandType.Text);
        }
        // Execute non-query with parameters using stored procedure
        public static int ExecuteProcNonQuery(string storedProcedureName, params SqlParameter[] _params)
        {
            return ExecuteNonQuery(storedProcedureName, _params, CommandType.StoredProcedure);
        }
        #endregion

        #region Play video
        public static string PlayVideoYoutube(string sID, int iWith, int iHeight)
        {
            string strHTML = "";
            strHTML += "<iframe title='YouTube video player'";
            strHTML += " width='" + iWith.ToString() + "' height='" + iHeight.ToString() + "'";
            strHTML += " src='http://www.youtube.com/embed/" + sID + "' ";
            strHTML += " frameborder='0' allowfullscreen></iframe>";
            return strHTML;
        }
        #endregion
    }
}